package com.hy.memoryassistant;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * Created by Jeffrey on 1/8/2015.
 */
public class ReminderSQLCollection implements ReminderCollection {
    private static String TAG = "ReminderSQLCollection";

    private SQLiteDatabase database;
    private ReminderSQLiteHelper dHelper;
    private String[] allColumns = {
            ReminderSQLiteHelper.COLUMN_ID,
            ReminderSQLiteHelper.COLUMN_TITLE,
            ReminderSQLiteHelper.COLUMN_PROMPT,
            ReminderSQLiteHelper.COLUMN_SUMMARY,
            ReminderSQLiteHelper.COLUMN_TAG,
            ReminderSQLiteHelper.COLUMN_EXT_LINK,
            ReminderSQLiteHelper.COLUMN_MEDIA_TYPE,
            ReminderSQLiteHelper.COLUMN_DATE_ADDED,
            ReminderSQLiteHelper.COLUMN_NEXT_NOTIFICATION,
            ReminderSQLiteHelper.COLUMN_NOTIFICATION_COUNT,
            ReminderSQLiteHelper.COLUMN_LAST_MODIFIED};

	private static ReminderSQLCollection instance = null;

	private ReminderSQLCollection(Context context) {
        dHelper = new ReminderSQLiteHelper(context);
    }

	public void open() throws SQLException {
         database = dHelper.getWritableDatabase();
     }

	/**
	 * Force single instance of ReminderSQLCollection
	 * 
	 * @param context
	 *            the context
	 * @return instance of ReminderSQLCollection
	 */
	public static ReminderSQLCollection getInstance(Context context) {
		if (instance == null) {
			instance = new ReminderSQLCollection(context);
			instance.open();
		}

		return instance;
     }
    /* Add a reminder
        r - The reminder to be added
     */
    public Reminder add(Reminder r){
        ContentValues values = new ContentValues(10);
        values.put(ReminderSQLiteHelper.COLUMN_TITLE, r.title);
        values.put(ReminderSQLiteHelper.COLUMN_PROMPT, r.prompt);
        values.put(ReminderSQLiteHelper.COLUMN_SUMMARY, r.summary);
        values.put(ReminderSQLiteHelper.COLUMN_TAG, r.tag);
        values.put(ReminderSQLiteHelper.COLUMN_EXT_LINK, r.ext_link);
        values.put(ReminderSQLiteHelper.COLUMN_MEDIA_TYPE, r.media_type);
        values.put(ReminderSQLiteHelper.COLUMN_DATE_ADDED, r.date_added);
        values.put(ReminderSQLiteHelper.COLUMN_NEXT_NOTIFICATION, r.next_notification);
        values.put(ReminderSQLiteHelper.COLUMN_NOTIFICATION_COUNT, r.notification_count);
        values.put(ReminderSQLiteHelper.COLUMN_LAST_MODIFIED, System.currentTimeMillis());

        long insertID = database.insert(ReminderSQLiteHelper.TABLE_REMINDERS, null, values);
        Cursor cursor = database.query(ReminderSQLiteHelper.TABLE_REMINDERS, allColumns,
                ReminderSQLiteHelper.COLUMN_ID + " = " + insertID, null, null, null, null);
        cursor.moveToFirst();

        Reminder newReminder = cursorToReminder(cursor);
        cursor.close();
        return newReminder;
    }

    private Reminder cursorToReminder(Cursor cursor) {
        Reminder newReminder = new Reminder();
        newReminder.ID = cursor.getLong(0);
        newReminder.title = cursor.getString(1);
        newReminder.prompt = cursor.getString(2);
        newReminder.summary = cursor.getString(3);
        newReminder.tag = cursor.getString(4);
        newReminder.ext_link = cursor.getString(5);
        newReminder.media_type = cursor.getInt(6);
        newReminder.date_added = cursor.getLong(7);
        newReminder.next_notification = cursor.getLong(8);
        newReminder.notification_count = cursor.getInt(9);
        newReminder.last_modified = cursor.getLong(10);
        return newReminder;
    }

    /* Get reminders on the Next_Notification date
        date - Date in millisec
       Return: array of reminders
     */
    public List<Reminder> get(long date) {
        Date curDate = new Date();
        curDate.setTime(date);

        SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");

        Date today = new Date();
        try {
            today = formatter.parse(formatter.format(curDate));
        } catch (ParseException e) {
            Log.e(TAG, e.toString());
        }
        Date tomorrow = new Date(today.getTime() + 1000 * 60 * 60 * 24);

        Log.d(TAG, String.format("Notification time: %d to %d", today.getTime(), tomorrow.getTime()));

        //long today = System.currentTimeMillis() - (System.currentTimeMillis() % (1000 * 60 * 60 * 24));
        return getWhere(ReminderSQLiteHelper.COLUMN_NEXT_NOTIFICATION + " > " + today.getTime() +
                " AND " + ReminderSQLiteHelper.COLUMN_NEXT_NOTIFICATION + " < " + tomorrow.getTime());
    }

    /* Get all reminders
       Return: all Reminders
     */
    public List<Reminder> getAll(){
        return getWhere(null);
    }

    /* Get reminders by tag
        tag - The tag ID
     */
    public List<Reminder> getByTag(int tag){
        return getWhere(ReminderSQLiteHelper.COLUMN_TAG + " = " + tag);
    }


    /**
     * Get reminder by id
     *
     * @param id reminder ID
     * @return Reminder
     */
    public Reminder getById(Long id) {
        Log.d(TAG, "GetById :" + id);
		List<Reminder> returnedResults = getWhere(ReminderSQLiteHelper.COLUMN_ID
				+ " = " + id);

        if (returnedResults != null && returnedResults.size() > 0)
            return (Reminder) returnedResults.get(0);
        return null;
    }

    /* Get all unique tags in the database
     */
    public String[] getTags(){
        Cursor cursor = database.query(ReminderSQLiteHelper.TABLE_REMINDERS,
                new String[]{ReminderSQLiteHelper.COLUMN_TAG},
                null,null,ReminderSQLiteHelper.COLUMN_TAG,null,ReminderSQLiteHelper.COLUMN_TAG);
        String[] tags = new String[cursor.getCount()];
        int i = 0;
        while (!cursor.isAfterLast()){
            tags[i++] = cursor.getString(0);
            cursor.moveToNext();
        }
        return tags;
    }

    /* Get Reminders by filters
        where - the where statement
     */
    private List<Reminder> getWhere(String where) {
        Log.i(TAG, "Where: " + where);
        List<Reminder> list = new ArrayList<Reminder>();
        Cursor cursor = database.query(ReminderSQLiteHelper.TABLE_REMINDERS, allColumns,
                where, null, null, null, null);

        cursor.moveToFirst();
        while (!cursor.isAfterLast()){
            list.add(cursorToReminder(cursor));
            cursor.moveToNext();
        }
        return list;
    }

    /* Delete reminder
     */
    public boolean remove(long ID){
        int rows = database.delete(ReminderSQLiteHelper.TABLE_REMINDERS, ReminderSQLiteHelper.COLUMN_ID + " = " + ID, null);
        return rows > 0;
    }
    public boolean remove(Reminder r){
        return remove(r.ID);
    }

    /**
     * Delete all records, DEV ONLY
     */
    public void removeAll() {
        database.execSQL("DROP TABLE IF EXISTS " + ReminderSQLiteHelper.TABLE_REMINDERS);
        database.execSQL(ReminderSQLiteHelper.DATABASE_CREATE);
    }
}
